﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class QAManager:ConnectionManager
    {
        public void Add(QAUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "INSERT INTO OQAS (DocNumber, DocDate, Remarks, Reference, ItemCode, ItemName, UoM, Qty, UnitPrice, LotNumber, MfrDate, ExpiryDate, Location, LocationName, DateCreated, CreatedBy, DateModified, ModifiedBy, DocStatus) VALUES (@DocNumber, @DocDate, @Remarks, @Reference, @ItemCode, @ItemName, @UoM, @Qty, @UnitPrice, @LotNumber, @MfrDate, @ExpiryDate, @Location, @LocationName, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy, @DocStatus)";
            
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", oUnit.DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
            oCommand.Parameters.Add(new SqlParameter("@Qty", oUnit.Qty));
            oCommand.Parameters.Add(new SqlParameter("@UnitPrice", oUnit.UnitPrice));
            oCommand.Parameters.Add(new SqlParameter("@LotNumber", oUnit.LotNumber));
            oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", oUnit.ExpiryDate));
            oCommand.Parameters.Add(new SqlParameter("@MfrDate", oUnit.ManufacturingDate));
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.LocationCode));
            oCommand.Parameters.Add(new SqlParameter("@LocationName", oUnit.LocationName));
            oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO QAS1 (DocNumber, ItemCode, ItemName, UoM, Qty, QAStatus, Location, LocationName, ControlNo) VALUES (@DocNumber, @ItemCode, @ItemName, @UoM, @Qty, @QAStatus, @Location, @LocationName, @ControlNo)";
                oCommand.Parameters.Add(new SqlParameter("@DocNumber", oUnit.DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
                oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));

                oCommand.Parameters.Add(new SqlParameter("@Location", oRow["Location"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@LocationName", oRow["LocationName"].ToString()));

                oCommand.Parameters.Add(new SqlParameter("@QAStatus", oRow["QAStatus"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ControlNo", oRow["ControlNo"].ToString()));

                oCommand.ExecuteNonQuery();
            }
        }

        public void Update(QAUnit oUnit)
        {

            SqlCommand oCommand = new SqlCommand();

            
            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "DELETE QAS1 WHERE DocNumber = @DocNumber";
            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand.CommandText = "INSERT INTO QAS1 (DocNumber, ItemCode, ItemName, UoM, Qty, QAStatus, ControlNo) VALUES (@DocNumber, @ItemCode, @ItemName, @UoM, @Qty, @QAStatus, @ControlNo))";
                    oCommand.Parameters.Add(new SqlParameter("@DocNumber", oUnit.DocNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@QAStatus", oRow["QAStatus"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ControlNo", oRow["ControlNo"].ToString()));

                    oCommand.ExecuteNonQuery();
                }
            }
        }

        public void ImportToStockCard(string sDocNumber)
        {
            SqlCommand oCommandHeader = new SqlCommand();
            SqlCommand oCommandLineItems = new SqlCommand();

            oCommandHeader.Connection = this.Connection;
            oCommandLineItems.Connection = this.Connection;
            //oCommand.CommandText = "INSERT INTO STOCKCARD SELECT QAS1.Location WhseCode, QAS1.LocationName as WhseName, QAS1.ItemCode, " 
            //    + "QAS1.ItemName, QAS1.Qty, OQAS.UoM, OQAS.LotNumber, OQAS.ExpiryDate, oqas.UnitPrice,  oqas.UnitPrice, QAS1.QAStatus, QAS1.ControlNo, " +
            //    " (SELECT OPDN.CardCode FROM OPDN WHERE OPDN.DocNum=OQAS.Reference) as VendorCode, (SELECT OPDN.CARDNAME FROM OPDN WHERE OPDN.DOCNUM=OQAS.REFERENCE) VendorName "+
            //    "FROM OQAS JOIN QAS1 ON OQAS.DocNumber = QAS1.DocNumber WHERE OQAS.DocNumber = @DOCNUMBER";
            //oCommand.Parameters.Add(new SqlParameter("@DocNumber", sDocNumber));
            
            //oCommand.ExecuteNonQuery();


            DataTable dtHeader = new DataTable();
            DataTable dtLineItems = new DataTable();
            
            SqlDataAdapter oAdapterHeader = new SqlDataAdapter();
            SqlDataAdapter oAdapterLineItems = new SqlDataAdapter();


            oCommandHeader.CommandText = "SELECT  (SELECT OPDN.CardCode FROM OPDN WHERE OPDN.DocNum=OQAS.Reference) as VendorCode, (SELECT OPDN.CARDNAME FROM OPDN WHERE OPDN.DOCNUM=OQAS.REFERENCE) VendorName, * FROM OQAS where DocNumber=@DocNum";
            oCommandHeader.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));

            oCommandLineItems.CommandText = "SELECT * FROM QAS1 where DocNumber=@DocNum";
            oCommandLineItems.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));

            oAdapterHeader.SelectCommand = oCommandHeader;
            oAdapterLineItems.SelectCommand = oCommandLineItems;


            oAdapterHeader.Fill(dtHeader);
            oAdapterLineItems.Fill(dtLineItems);

            foreach (DataRow oRow in dtLineItems.Rows)
            {
                StockManager oManager = new StockManager();

                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(oRow["Location"].ToString(), oRow["ItemCode"].ToString(), dtHeader.Rows[0]["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(oRow["Location"].ToString(), oRow["ItemCode"].ToString(), dtHeader.Rows[0]["LotNumber"].ToString(), oRow["Qty"].ToString(), dtHeader.Rows[0]["UnitPrice"].ToString());
                }
                else
                {
                    oManager.AddStocks(oRow["Location"].ToString(), oRow["LocationName"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["Qty"].ToString(), oRow["UoM"].ToString(), dtHeader.Rows[0]["LotNumber"].ToString(), dtHeader.Rows[0]["MfrDate"].ToString(), dtHeader.Rows[0]["ExpiryDate"].ToString(), dtHeader.Rows[0]["UnitPrice"].ToString(), dtHeader.Rows[0]["VendorCode"].ToString(), dtHeader.Rows[0]["VendorName"].ToString());
                }

                
            }

        }

        public void ReleaseQuarantinedItem(string GRPONumber, string VendorCode, string ItemCode, string LotNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OQAITM SET ItemStatus = 'Released' WHERE DOCNUM=@DOCNUM AND CARDCODE = @CARDCODE AND ITEMCODE = @ITEMCODE AND LOTNUMBER = @LOTNUMBER";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", GRPONumber));
            oCommand.Parameters.Add(new SqlParameter("@CARDCODE", VendorCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@LOTNUMBER", LotNumber));


            oCommand.ExecuteNonQuery();
        }

        public DataTable GetByDocumentNumber(string sDocNumber)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter _adapter = new SqlDataAdapter();
            SqlCommand _SELECT_CMD = new SqlCommand();

            _SELECT_CMD.Connection = this.Connection;
            _SELECT_CMD.CommandText = "SELECT * FROM OQAS WHERE DocNumber=@DocNumber";

            _adapter.SelectCommand = _SELECT_CMD;
            _adapter.Fill(dt);

            return dt;
        }

        public DataTable GetByLineItems(string sDocNumber)
        {

            DataTable dt = new DataTable();
            SqlDataAdapter _adapter = new SqlDataAdapter();
            SqlCommand _SELECT_CMD = new SqlCommand();

            _SELECT_CMD.Connection = this.Connection;
            _SELECT_CMD.CommandText = "SELECT * FROM QAS1 WHERE DocNumber=@DocNumber";

            _adapter.SelectCommand = _SELECT_CMD;
            _adapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseByDocNum(String sDocNumber)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter _adapter = new SqlDataAdapter();
            SqlCommand _SELECT_CMD = new SqlCommand();

            sDocNumber = "%" + sDocNumber.Trim() +"%";

            _SELECT_CMD.Connection = this.Connection;
            _SELECT_CMD.CommandText = "SELECT * FROM OQAS WHERE DocNumber like @DocNumber";

            _adapter.SelectCommand = _SELECT_CMD;
            _adapter.Fill(dt);

            return dt;
        }
        
    }
}
